package com.yanxx.codegenerator.storage.impl;

import com.yanxx.codegenerator.storage.CodeGeneratorState;
import com.yanxx.codegenerator.storage.CodeGeneratorStorage;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Set;

/**
 * 数据库存储实现
 * 使用关系型数据库存储编号数据
 */
public class DatabaseStorage implements CodeGeneratorStorage {
    private static final Logger logger = LoggerFactory.getLogger(DatabaseStorage.class);

    private final DataSource dataSource;
    private final String generatorId;

    /**
     * 构造函数
     *
     * @param dataSource  数据源
     * @param generatorId 生成器ID，用于区分不同的生成器实例
     */
    public DatabaseStorage(DataSource dataSource, String generatorId) {
        this.dataSource = dataSource;
        this.generatorId = generatorId;
        initTables();
    }

    /**
     * 初始化数据库表
     */
    private void initTables() {
        try (Connection conn = dataSource.getConnection()) {
            // 创建生成器状态表
            String createStateTableSql = "CREATE TABLE IF NOT EXISTS code_generator_state (" +
                    "generator_id VARCHAR(100) PRIMARY KEY, " +
                    "current_max_value BIGINT NOT NULL" +
                    ")";

            // 创建编号集合表
            String createCodesTableSql = "CREATE TABLE IF NOT EXISTS code_generator_codes (" +
                    "generator_id VARCHAR(100) NOT NULL, " +
                    "code VARCHAR(100) NOT NULL, " +
                    "code_type VARCHAR(20) NOT NULL, " + // 'confirmed', 'pending', 'recycled'
                    "PRIMARY KEY (generator_id, code)" +
                    ")";

            try (PreparedStatement stmtState = conn.prepareStatement(createStateTableSql);
                    PreparedStatement stmtCodes = conn.prepareStatement(createCodesTableSql)) {
                stmtState.executeUpdate();
                stmtCodes.executeUpdate();
            }

            logger.info("Database tables initialized for generator: {}", generatorId);
        } catch (SQLException e) {
            logger.error("Failed to initialize database tables: {}", e.getMessage(), e);
        }
    }

    @Override
    public boolean save(long currentMaxValue, Set<String> confirmedCodes, Set<String> pendingCodes,
            Set<String> recycledCodes) {
        try (Connection conn = dataSource.getConnection()) {
            conn.setAutoCommit(false);

            try {
                // 保存当前最大值 - 使用MySQL兼容的语法
                String upsertStateSql = "INSERT INTO code_generator_state (generator_id, current_max_value) VALUES (?, ?) "
                        +
                        "ON DUPLICATE KEY UPDATE current_max_value = VALUES(current_max_value)";
                try (PreparedStatement stmt = conn.prepareStatement(upsertStateSql)) {
                    stmt.setString(1, generatorId);
                    stmt.setLong(2, currentMaxValue);
                    stmt.executeUpdate();
                }

                // 删除旧的编号数据
                String deleteCodesSql = "DELETE FROM code_generator_codes WHERE generator_id = ?";
                try (PreparedStatement stmt = conn.prepareStatement(deleteCodesSql)) {
                    stmt.setString(1, generatorId);
                    stmt.executeUpdate();
                }

                // 批量插入编号数据
                String insertCodeSql = "INSERT INTO code_generator_codes (generator_id, code, code_type) VALUES (?, ?, ?)";
                try (PreparedStatement stmt = conn.prepareStatement(insertCodeSql)) {
                    // 插入已确认的编号
                    for (String code : confirmedCodes) {
                        stmt.setString(1, generatorId);
                        stmt.setString(2, code);
                        stmt.setString(3, "confirmed");
                        stmt.addBatch();
                    }

                    // 插入待回收的编号
                    for (String code : pendingCodes) {
                        stmt.setString(1, generatorId);
                        stmt.setString(2, code);
                        stmt.setString(3, "pending");
                        stmt.addBatch();
                    }

                    // 插入已回收的编号
                    for (String code : recycledCodes) {
                        stmt.setString(1, generatorId);
                        stmt.setString(2, code);
                        stmt.setString(3, "recycled");
                        stmt.addBatch();
                    }

                    stmt.executeBatch();
                }

                conn.commit();
                logger.info("Successfully saved state to database for generator: {}", generatorId);
                return true;
            } catch (SQLException e) {
                conn.rollback();
                logger.error("Failed to save state to database: {}", e.getMessage(), e);
                return false;
            } finally {
                conn.setAutoCommit(true);
            }
        } catch (SQLException e) {
            logger.error("Failed to get database connection: {}", e.getMessage(), e);
            return false;
        }
    }

    @Override
    public CodeGeneratorState load() {
        try (Connection conn = dataSource.getConnection()) {
            // 加载当前最大值
            String selectStateSql = "SELECT current_max_value FROM code_generator_state WHERE generator_id = ?";
            long currentMaxValue = 0;
            boolean stateExists = false;

            try (PreparedStatement stmt = conn.prepareStatement(selectStateSql)) {
                stmt.setString(1, generatorId);
                try (ResultSet rs = stmt.executeQuery()) {
                    if (rs.next()) {
                        currentMaxValue = rs.getLong("current_max_value");
                        stateExists = true;
                    }
                }
            }

            if (!stateExists) {
                logger.warn("No state found in database for generator: {}", generatorId);
                return null;
            }

            // 加载编号数据
            Set<String> confirmedCodes = new HashSet<>();
            Set<String> pendingCodes = new HashSet<>();
            Set<String> recycledCodes = new HashSet<>();

            String selectCodesSql = "SELECT code, code_type FROM code_generator_codes WHERE generator_id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(selectCodesSql)) {
                stmt.setString(1, generatorId);
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        String code = rs.getString("code");
                        String codeType = rs.getString("code_type");

                        switch (codeType) {
                            case "confirmed":
                                confirmedCodes.add(code);
                                break;
                            case "pending":
                                pendingCodes.add(code);
                                break;
                            case "recycled":
                                recycledCodes.add(code);
                                break;
                            default:
                                logger.warn("Unknown code type: {} for code: {}", codeType, code);
                        }
                    }
                }
            }

            // 创建状态对象
            CodeGeneratorState state = new CodeGeneratorState();
            state.setCurrentMaxValue(currentMaxValue);
            state.setConfirmedCodes(confirmedCodes);
            state.setPendingCodes(pendingCodes);
            state.setRecycledCodes(recycledCodes);

            logger.info(
                    "Successfully loaded state from database for generator: {}, currentMaxValue: {}, confirmedCodes: {}, pendingCodes: {}, recycledCodes: {}",
                    generatorId, currentMaxValue, confirmedCodes.size(), pendingCodes.size(), recycledCodes.size());

            return state;
        } catch (SQLException e) {
            logger.error("Failed to load state from database: {}", e.getMessage(), e);
            return null;
        }
    }

    @Override
    public boolean clear() {
        try (Connection conn = dataSource.getConnection()) {
            conn.setAutoCommit(false);

            try {
                // 删除状态数据
                String deleteStateSql = "DELETE FROM code_generator_state WHERE generator_id = ?";
                try (PreparedStatement stmt = conn.prepareStatement(deleteStateSql)) {
                    stmt.setString(1, generatorId);
                    stmt.executeUpdate();
                }

                // 删除编号数据
                String deleteCodesSql = "DELETE FROM code_generator_codes WHERE generator_id = ?";
                try (PreparedStatement stmt = conn.prepareStatement(deleteCodesSql)) {
                    stmt.setString(1, generatorId);
                    stmt.executeUpdate();
                }

                conn.commit();
                logger.info("Successfully cleared state in database for generator: {}", generatorId);
                return true;
            } catch (SQLException e) {
                conn.rollback();
                logger.error("Failed to clear state in database: {}", e.getMessage(), e);
                return false;
            } finally {
                conn.setAutoCommit(true);
            }
        } catch (SQLException e) {
            logger.error("Failed to get database connection: {}", e.getMessage(), e);
            return false;
        }
    }
}